﻿using BaseData.CommonLib.Models;
using Dapper;
using Swimj.CommonLib.Base;
using System.Text;
using Microsoft.Data.SqlClient;
using Swimj.DataCore;

namespace BaseData.ApplicationLib.Queries
{
    public class CalculateUnitQueries : ICalculateUnitQueries
    {
        private readonly string? _defaultConnStr;

        public CalculateUnitQueries(string? defaultConnStr)
        {
            _defaultConnStr = defaultConnStr;
        }

        public async Task<Result<CalculateUnitInfoResponseDto>> GetUnit(int id)
        {
            CalculateUnitInfoResponseDto? data;
            using (var db = new SqlConnection(_defaultConnStr))
            {
                // var db = new QueryFactory()

                StringBuilder sqlBuilder = new StringBuilder(@"select id,
            code,
            name,
            description,
            datastate,
            createdby,
            createdon,
            lastmodifiedby,
            lastmodifiedon,
            deletedby,
            deletedon
                from MES_CALCULATEUNIT where 1 = 1 and id = @Id");


                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("Id", id);
                data = await db.QueryFirstOrDefaultAsync<CalculateUnitInfoResponseDto>(sqlBuilder.ToString(),
                    parameters);
            }

            return await Result<CalculateUnitInfoResponseDto>.SuccessAsync(data);
        }

        public async Task<Result<IList<CalculateUnitInfoResponseDto>>> QueryUnits(CalculateUnitSearchRequestDto model)
        {
            IEnumerable<CalculateUnitInfoResponseDto>? data;
            using (var db = new SqlConnection(_defaultConnStr))
            {
                StringBuilder sqlBuilder = new StringBuilder(@"select id,
            code,
            name,
            description,
            datastate,
            createdby,
            createdon,
            lastmodifiedby,
            lastmodifiedon,
            deletedby,
            deletedon
                from MES_CALCULATEUNIT where 1 = 1 ");

                DynamicParameters parameters = new DynamicParameters();

                data = await db.QueryAsync<CalculateUnitInfoResponseDto>(sqlBuilder.ToString(), parameters);
            }

            return await Result<IList<CalculateUnitInfoResponseDto>>.SuccessAsync(data.ToList());
        }

        public async Task<PaginatedResult<CalculateUnitInfoResponseDto>> QueryUnitsByPage(
            CalculateUnitSearchRequestDto model)
        {
            IEnumerable<CalculateUnitInfoResponseDto>? data = new List<CalculateUnitInfoResponseDto>();
            int totalCount = 0;
            using (var db = new SqlConnection(_defaultConnStr))
            {
                var query = new Query(db);

                StringBuilder sqlBuilder = new StringBuilder(@"
select id,
       code,
       name,
       description,
       datastate,
       createdby,
       createdon,
       lastmodifiedby,
       lastmodifiedon,
       deletedby,
       deletedon
  from MES_CALCULATEUNIT where 1 = 1 ");

                DynamicParameters parameters = new();

                if (!string.IsNullOrEmpty(model.Code))
                {
                    sqlBuilder.Append(" and code like @Code ");
                    parameters.Add("Code", $"%{model.Code}%");
                }

                if (!string.IsNullOrEmpty(model.Name))
                {
                    sqlBuilder.Append(" and name like @Name");
                    parameters.Add("Name", $"%{model.Name}%");
                }

                query.BuildPageQueries(model.Page, model.Limit, sqlBuilder.ToString());

                data = await db.QueryAsync<CalculateUnitInfoResponseDto>(query.SqlPage, parameters);
                //
                // var totalSqlBuilder = new StringBuilder(@"select count(1) from MES_CALCULATEUNIT t where 1 = 1 ");
                //
                // DynamicParameters totalParameters = new();
                //
                // if (!string.IsNullOrEmpty(model.Code))
                // {
                //     totalSqlBuilder.Append(" and code like @Code ");
                //     totalParameters.Add("Code", $"%{model.Code}%");
                // }
                //
                // if (!string.IsNullOrEmpty(model.Name))
                // {
                //     totalSqlBuilder.Append(" and name like @Name");
                //     totalParameters.Add("Name", $"%{model.Name}%");
                // }
                //
                totalCount = await db.ExecuteScalarAsync<int>(query.SqCount, parameters);
            }

            return await PaginatedResult<CalculateUnitInfoResponseDto>.SuccessAsync(data.ToList(), totalCount,
                model.Page,
                model.Limit);
        }
    }
}